const { Sequelize } = require('sequelize');
const logger = require('../utils/logger');

// 加载环境变量
require('dotenv').config({ path: `.env.${process.env.NODE_ENV || 'development'}` });

// 数据库配置
const config = {
  development: {
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'okx_robot_dev',
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    dialect: 'mysql',
    logging: (msg) => logger.debug(msg),
    pool: {
      max: 10,
      min: 0,
      acquire: 30000,
      idle: 10000
    },
    timezone: '+08:00'
  },
  test: {
    username: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'okx_robot_test',
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    dialect: 'mysql',
    logging: false,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    },
    timezone: '+08:00'
  },
  production: {
    username: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOST,
    port: process.env.DB_PORT || 3306,
    dialect: 'mysql',
    logging: false,
    pool: {
      max: 20,
      min: 5,
      acquire: 30000,
      idle: 10000
    },
    timezone: '+08:00'
  }
};

// 创建 Sequelize 实例
const sequelize = new Sequelize(
  config[process.env.NODE_ENV || 'development']
);

// 测试连接
const testConnection = async () => {
  try {
    await sequelize.authenticate();
    logger.info('✅ Database connection established successfully.');
    return true;
  } catch (error) {
    logger.error('❌ Unable to connect to the database:', error);
    return false;
  }
};

// 同步数据库（开发环境使用）
const syncDatabase = async (force = false) => {
  try {
    await sequelize.sync({ force });
    logger.info(`✅ Database synchronized successfully. Force: ${force}`);
    return true;
  } catch (error) {
    logger.error('❌ Database synchronization failed:', error);
    return false;
  }
};

// 关闭连接
const closeConnection = async () => {
  try {
    await sequelize.close();
    logger.info('✅ Database connection closed successfully.');
  } catch (error) {
    logger.error('❌ Error closing database connection:', error);
  }
};

// 导出配置（供 Sequelize CLI 使用）
module.exports = config;

// 导出实例和方法（供应用使用）
module.exports.sequelize = sequelize;
module.exports.testConnection = testConnection;
module.exports.syncDatabase = syncDatabase;
module.exports.closeConnection = closeConnection;

// 兼容旧版本的连接方法
module.exports.connectDB = testConnection;
module.exports.query = async (sql, params = []) => {
  try {
    const [results] = await sequelize.query(sql, {
      replacements: params,
      type: sequelize.QueryTypes.SELECT
    });
    return results;
  } catch (error) {
    logger.error('Query error:', error);
    throw error;
  }
};
